﻿// ================================================================================
// 		File: NpoiHelper.cs
// 		Desc: 导入
//  
// 		Called by:   
//               
// 		Auth: chenwenzhi（chenwenzhi@sxt.com.cn）
// 		Date: 2013-10-29 
// ================================================================================
// 		Change History
// ================================================================================
// 		Date:		Author:				Description:
// 		--------	--------			-------------------
//    
// ================================================================================
// Copyright (C) 2008-2013 SXT Corporation
// ================================================================================

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NPOI.SS.UserModel;
using System.IO;
using System.Data;
using FineUI;
using ZHDJ.Core.Util;

namespace ZHDJ.Core.Office
{
    public class NpoiHelper
    {
        /// <summary>
        /// excel转为ds
        /// </summary>
        /// <param name="fuFile"></param>
        /// <param name="sheetName"></param>
        /// <param name="startRow"></param>
        /// <param name="startCol"></param>
        /// <param name="colSize"></param>
        /// <returns></returns>
        public DataSet ReadToDataSet(FileUpload fuFile, string sheetName, int startRow, int startCol, int colSize)
        {
            if (!string.IsNullOrEmpty(fuFile.FileName))
            {
                string strExt = fuFile.FileName.Substring(fuFile.FileName.LastIndexOf(".") + 1);
                string strFileFullPath = UploadFileToTempDirectory(fuFile); //上传临时文件
                List<string[]> lstSheetData = new List<string[]>();
                try
                {
                    IWorkbook excelBook = null;
                    using (FileStream stream = new FileStream(strFileFullPath, FileMode.Open, FileAccess.Read))
                    { excelBook = WorkbookFactory.Create(stream); }

                    int emptyRowCounts = 0, currentRowIndex = startRow - 1;
                    ISheet excelSheet = string.IsNullOrEmpty(sheetName) ? excelBook.GetSheetAt(0) : excelBook.GetSheet(sheetName);
                    //ISheet excelSheet = excelBook.GetSheetAt(0);//默认读取第一个sheet

                    while (true)
                    {
                        if (emptyRowCounts >= 3)
                        { break; }

                        bool isEmptyRow = true;
                        string[] arrCols = new string[colSize];

                        IRow sheetRow = excelSheet.GetRow(currentRowIndex);

                        if (sheetRow == null)
                        { goto SKIP_CONTINUE; }

                        for (int i = 0; i < colSize; i++)
                        {
                            ICell sheetCell = sheetRow.GetCell(startCol - 1 + i);

                            if (sheetCell == null)
                            {
                                arrCols[i] = null;
                                continue;
                            }

                            arrCols[i] = sheetCell.ToString();

                            if (!string.IsNullOrEmpty(arrCols[i]))
                            { isEmptyRow = false; }
                        }

                        SKIP_CONTINUE:
                        if (isEmptyRow)
                        { emptyRowCounts++; }
                        else
                        {
                            emptyRowCounts = 0;
                            lstSheetData.Add(arrCols);
                        }

                        currentRowIndex++;
                    }
                }
                catch (Exception ex)
                { }
                finally
                {
                    GC.Collect();
                }
                DeleteTempFile(strFileFullPath);
                return ConvertToDataSet(lstSheetData.ToArray());
            }

            return null;
        }

        private string UploadFileToTempDirectory(FileUpload fuFile)
        {
            string uploadTempDirectory = UploadTempDirectory;
            string str2 = fuFile.FileName.Substring(fuFile.FileName.LastIndexOf("."));
            uploadTempDirectory = uploadTempDirectory + StringHelper.NewID + str2;
            if (CreateTempDirectory(uploadTempDirectory))
            {
                fuFile.SaveAs(uploadTempDirectory);
                return uploadTempDirectory;
            }
            return "";
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        private DataSet ConvertToDataSet(string[][] data)
        {
            if (data == null)
            {
                return null;
            }
            if (data.Length < 1)
            {
                return null;
            }
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            var listCol = data[0].ToList();
            int index = 0;
            var isDis = listCol.Distinct().Count() < listCol.Count();
            foreach (string value in data[0])
            {

                if (isDis)
                {
                    //有重复项，用数字
                    dt.Columns.Add(value + index, typeof(string));
                    index += 1;
                }
                else
                {
                    dt.Columns.Add(value, typeof (string));
                }
            }
            if (data.Length > 1)
            {
                for (int i = 1; i < data.Length; i++)
                {
                    DataRow dr = dt.NewRow();
                    for (int j = 0; j < data[i].Length; j++)
                    {
                        dr[j] = data[i][j];
                    }
                    dt.Rows.Add(dr);
                }
            }
            ds.Tables.Add(dt);
            return ds;
        }

        /// <summary>
        /// 临时文件夹
        /// </summary>
        private string UploadTempDirectory
        {
            get
            {
                string str = HttpContext.Current.Server.MapPath("~/");
                if (!str.EndsWith(@"\"))
                {
                    str = str + @"\";
                }
                return (str + @"FileUploadTemp\");
            }
        }

        /// <summary>
        /// 创建临时文件夹
        /// </summary>
        /// <param name="fileFullPath">路径</param>
        /// <returns></returns>
        private bool CreateTempDirectory(string fileFullPath)
        {
            try
            {
                FileInfo info = new FileInfo(fileFullPath);
                DirectoryInfo info2 = new DirectoryInfo(info.DirectoryName);
                if (!info2.Exists)
                {
                    info2.Create();
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 删除临时文件 
        /// </summary>
        /// <param name="fileFullPath"></param>
        /// <returns></returns>
        private bool DeleteTempFile(string fileFullPath)
        {
            try
            {
                new FileInfo(fileFullPath).Delete();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }
}